USE [DSP]
GO
/****** Object:  View [dbo].[vw_TestDetail]    Script Date: 07/23/2019 15:59:27 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_TestDetail]'))
DROP VIEW [dbo].[vw_TestDetail]
GO
/****** Object:  View [dbo].[vw_Tests]    Script Date: 07/23/2019 15:59:27 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_Tests]'))
DROP VIEW [dbo].[vw_Tests]

USE [DSP]
GO

/****** Object:  View [dbo].[vw_Tests]    Script Date: 07/23/2019 15:59:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_Tests]
AS
SELECT     dbo.Tests.GroupID, dbo.Groups.GroupCode, dbo.Groups.GroupName, dbo.Groups.GroupNameUrdu, dbo.Groups.Type AS GroupType, dbo.Tests.TestID, 
                      dbo.Tests.TestCode, dbo.Tests.TestName, dbo.Tests.DisplayTitle, dbo.Tests.UnitTitle, dbo.Tests.NormalValue, dbo.Tests.TestRate, 
                      dbo.Tests.Type
FROM         dbo.Tests LEFT OUTER JOIN
                      dbo.Groups ON dbo.Tests.GroupID = dbo.Groups.GroupID

GO

/****** Object:  View [dbo].[vw_TestDetail]    Script Date: 07/23/2019 15:59:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_TestDetail]
AS
SELECT     dbo.Voucher.VoucherID, dbo.Voucher.VoucherDate, dbo.Voucher.VoucherNo, dbo.Voucher.SessionID, dbo.SessionInfo.SessionTitle, 
                      dbo.Voucher.Remarks, dbo.Voucher.ReadOnly, dbo.Voucher.BankAccountID, dbo.Accounts.AccountTitle, dbo.Voucher.LoginID, 
                      dbo.Employees.EmployeeName, dbo.Voucher.HostName, dbo.Voucher.EntryDateTime, dbo.Voucher.ShiftID, dbo.Shifts.ShiftTitle, 
                      dbo.Voucher.ReportDate, dbo.Voucher.PatientID, dbo.Voucher.PatientName, dbo.Voucher.Age, dbo.Voucher.Gender, dbo.Voucher.CellNo, 
                      dbo.Voucher.Address, dbo.Voucher.Amount, dbo.Voucher.Discount, dbo.Voucher.Expense, dbo.Voucher.TotalAmount, dbo.Voucher.DrPercent, 
                      dbo.Voucher.DrAmount, dbo.Voucher.NetAmount, dbo.Voucher.CashReceived, dbo.Voucher.Balance, dbo.Voucher.ModifyID, 
                      dbo.Voucher.ModifyHostName, dbo.Voucher.ModifyDateTime, dbo.VoucherDetailsTest.TestDetailID, dbo.VoucherDetailsTest.GroupID, 
                      CASE WHEN dbo.Tests.Type = 0 THEN dbo.Tests.TestName ELSE Tests_1.TestName END AS GroupName, 
                      CASE WHEN dbo.Tests.Type = 0 THEN Tests.TestRate ELSE Tests_1.TestRate END AS TestRate, dbo.VoucherDetailsTest.TestID, dbo.Tests.TestCode, 
                      dbo.Tests.TestName, dbo.Tests.Type, CASE WHEN dbo.Tests.Type = 0 THEN dbo.Tests.DisplayTitle ELSE Tests_1.DisplayTitle END AS DisplayTitle, 
                      dbo.Tests.UnitTitle, dbo.Tests.NormalValue, dbo.VoucherDetailsTest.PatientValue, dbo.VoucherDetailsTest.DetailRemarks, 
                      dbo.Voucher.TestNotes
FROM         dbo.Tests LEFT OUTER JOIN
                      dbo.Tests Tests_1 ON dbo.Tests.GroupID = Tests_1.TestID RIGHT OUTER JOIN
                      dbo.Voucher INNER JOIN
                      dbo.VoucherDetailsTest ON dbo.Voucher.VoucherID = dbo.VoucherDetailsTest.VoucherID ON 
                      dbo.Tests.TestID = dbo.VoucherDetailsTest.TestID LEFT OUTER JOIN
                      dbo.Employees ON dbo.Voucher.LoginID = dbo.Employees.EmployeeID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.Voucher.BankAccountID = dbo.Accounts.AccountNo LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID LEFT OUTER JOIN
                      dbo.Shifts ON dbo.Voucher.ShiftID = dbo.Shifts.ShiftID

GO